/*** This do-file creates county-level private sector employment in each CZ in
the QCEW.
***/

*-------------------------------------------------------------------------------
* Set up
*-------------------------------------------------------------------------------

* Set $root 
project figstabs, root
if (r(buildrunning)==0) include "${root}/code/config_interactive.do"

* Set globals
project, uses("${root}/code/set_globals.do")
include "${root}/code/set_globals.do"
local category "Employment"

cap mkdir "${root}/data/derived/QCEW"

*-------------------------------------------------------------------------------
* Process files
*-------------------------------------------------------------------------------

* Get list of files 
cd "${root}/data/derived/QCEW"

* Loop over list of files 
forval year = 2020/2021  {
	
	* Unzip ZIP files 
	project, uses("${root}/data/dvc/QCEW/`year'_qtrly_by_area.zip")
	unzipfile "${root}/data/dvc/QCEW/`year'_qtrly_by_area.zip", replace
	
	loc i = 0 
	loc county_files : dir "${root}/data/derived/QCEW/`year'.q1-q4.by_area" files "*.csv"
	
	* Count total number of files 
	foreach file in `county_files' {

	if !strpos("`file'", "statewide") & !strpos("`file'", "MSA") & !strpos("`file'", "MicroSA") & !strpos("`file'", "CSA") ///
		& !strpos("`file'", " msa") & !strpos("`file'", " microsa") & !strpos("`file'", "csa") ///
		& !strpos("`file'", "combined statistical area") & !strpos("`file'", "u.s. total") /// 
		& !strpos("`file'", "metropolitan statistical area") & !strpos("`file'", "combined") & !strpos("`file'", "U.S. TOTAL") & !strpos("`file'", "Unknown Or Undefined") ///
		& !strpos("`file'", "Unknown Or Undef.") {
	
			* Add one to ticker 
			loc i = `i' + 1 

			* Import data 
			project, uses("${root}/data/derived/QCEW/`year'.q1-q4.by_area/`file'")
			import delimited "${root}/data/derived/QCEW/`year'.q1-q4.by_area/`file'", clear 

			* Confirm structure 
			gisid own_title industry_code agglvl_code size_code year qtr

			* Keep private sector
			keep if own_title == "Private" & inrange(qtr, 1, 4)
		
			* Keep cut by NAICS  
			keep if agglvl_title == "County, NAICS Sector -- by ownership sector"
		
			* Confirm structure 
			gisid industry_code qtr 

			* Keep relevant 
			keep area_fips industry_code industry_title month1_emplvl month2_emplvl month3_emplvl qtr year
		
			* Rename 
			rename month1_emplvl qcew_employment1
			rename month2_emplvl qcew_employment2
			rename month3_emplvl qcew_employment3
			rename area_fips countyfips

			* Append together 
			if `i' == 1 {
				tempfile appended 
				save `appended', replace
			}
			else {
				append using `appended'
				save `appended', replace
			}
		}
	}

	save "${root}/data/derived/QCEW/qcew_`year'.dta", replace 
	project, creates("${root}/data/derived/QCEW/qcew_`year'.dta")
}


* Append datasets 
project, uses("${root}/data/derived/QCEW/qcew_2020.dta")
project, uses("${root}/data/derived/QCEW/qcew_2021.dta")

use "${root}/data/derived/QCEW/qcew_2020.dta", clear 
append using "${root}/data/derived/QCEW/qcew_2021.dta"
cap drop naics_code

* Reshape (no greshape due to string variable)
reshape long qcew_employment, i(qtr year countyfips industry_code industry_title) j(month)
replace qcew_employment = 0 if mi(qcew_employment) 

* Get months 
replace month = 4 if month == 1 & qtr == 2
replace month = 5 if month == 2 & qtr == 2
replace month = 6 if month == 3 & qtr == 2

replace month = 7 if month == 1 & qtr == 3
replace month = 8 if month == 2 & qtr == 3
replace month = 9 if month == 3 & qtr == 3

replace month = 10 if month == 1 & qtr == 4
replace month = 11 if month == 2 & qtr == 4
replace month = 12 if month == 3 & qtr == 4

* Sort
sort countyfips industry_code industry_title year month
gisid countyfips industry_code year month

compress

*-------------------------------------------------------------------------------
* Save
*-------------------------------------------------------------------------------

save "${root}/data/derived/QCEW/QCEW by county x industry.dta", replace  
project, creates("${root}/data/derived/QCEW/QCEW by county x industry.dta")
